package nbcp.myoql.db.sql.base

import nbcp.base.comm.JsonMap
import nbcp.base.extend.*
import nbcp.base.utils.CodeUtil
import nbcp.myoql.db.db
import nbcp.myoql.db.sql.component.SqlQueryClip
import nbcp.myoql.db.sql.component.WhereData
import nbcp.myoql.db.sql.enums.DbType
import nbcp.myoql.db.sql.extend.jsonContains
import nbcp.myoql.db.sql.extend.jsonEquals
import nbcp.myoql.db.sql.extend.jsonLength
import nbcp.myoql.db.sql.extend.proc_value
import java.io.Serializable

abstract class BaseAliasSqlSect : Serializable {
    var aliaValue: String = ""

    abstract fun toSingleSqlData(): SqlParameterData;
    open fun getAliasName(): String {
        return this.aliaValue
    }


    private fun sqlMatch(op: String, value: Serializable): WhereData {
        var valueValue = proc_value(value);
        if (this is SqlColumnName) {
            return WhereData(
                "${this.fullName} ${op} :${this.paramVarKeyName}",
                JsonMap(this.paramVarKeyName to valueValue)
            )
        }

        var opKey = CodeUtil.getCode();
        var sqlData = this.toSingleSqlData();
        return WhereData(
            "${sqlData.expression} ${op} :${opKey}",
            JsonMap(opKey to valueValue)
        )
    }


    /**
     * like 操作
     * @param value: 可以包含合法的 %,_
     */
    infix fun like(value: String): WhereData = this.sqlMatch("like", value)

    /**
     * like "%查询内容%"
     */
    infix fun likeAll(value: String): WhereData = this.sqlMatch("like", ("%" + value + "%"))


    /**
     * 相等操作, 也可以比较 Json 数组的相等。
     */
    infix fun sqlEquals(value: Serializable): WhereData {
        if (this is SqlColumnName) {
            if (value is SqlColumnName) {
                return WhereData("${this.fullName} = ${value.fullName}")
            }

            //仅支持 数组相等。
            if (this.dbType == DbType.JSON) {
                val json_equals = this.jsonEquals(value.ToJson())

                return WhereData(
                    "${json_equals.expression} = 1",
                    json_equals.values
                )
            }
        }


        return this.sqlMatch("=", value);
    }

    /**
     * 相等操作, 也可以比较 Json 数组内容的相等。
     */
    infix fun sqlArrayContentEquals(value: Serializable): WhereData {
        if (this is SqlColumnName) {
            if (value is SqlColumnName) {
                return WhereData("${this.fullName} = ${value.fullName}")
            }

            //仅支持 数组相等。
            if (this.dbType == DbType.JSON) {
                val v_type = value::class.java
                val json_length = this.jsonLength()
                if (v_type.isArray) {
                    var ary = value as Array<Any?>;
                    val json_contains = this.jsonContains(db.sql.json_array(ary.toList()))
                    return WhereData(
                        "${json_length.expression} = ${ary.size} and ${json_contains.expression} = 1",
                        json_length.values + json_contains.values
                    )
                } else if (v_type.IsCollectionType) {
                    var ary = value as Collection<Any?>;
                    val json_contains = this.jsonContains(db.sql.json_array(ary))
                    return WhereData(
                        "${json_length.expression} = ${ary.size} and ${json_contains.expression} = 1",
                        json_length.values + json_contains.values
                    )
                } else {
                    val json_contains = this.jsonContains(db.sql.json_array(listOf(value)))
                    return WhereData(
                        "${json_length.expression} = 1 or ${json_contains.expression} = 1",
                        json_length.values + json_contains.values
                    )
                }
            }
        }

        return this.sqlMatch("=", value);
    }

    infix fun sqlRegexp(value: String): WhereData {
        return this.sqlMatch("regexp", value);
    }

    /*
    * 不等操作
    */
    infix fun sqlNotEquals(value: Serializable): WhereData {
        if (this is SqlColumnName) {
            if (value is SqlColumnName) {
                return WhereData("${this.fullName} != ${value.fullName}")
            }

            //仅支持 数组相等。
            if (this.dbType == DbType.JSON) {
                val json_equals = this.jsonEquals(value.ToJson())

                return WhereData(
                    "${json_equals.expression} = 0",
                    json_equals.values
                )
            }
        }
        return this.sqlMatch("!=", value);
    }

    infix fun sqlJsonArrayContains(value: Serializable): WhereData {
        if (value is SqlColumnName) {
            return WhereData("${this.jsonContains(value)} = 1")
        }

        if (this is SqlColumnName) {
            //仅支持 数组相等。
            if (this.dbType == DbType.JSON) {
                val v_type = value::class.java

                if (v_type.isArray) {
                    val ary = value as Array<Any?>;
                    val json_contains = this.jsonContains(db.sql.json_array(ary.toList()))
                    return WhereData("${json_contains.expression} = 1", json_contains.values)
                } else if (v_type.IsCollectionType) {
                    val ary = value as Collection<Any?>;
                    val json_contains = this.jsonContains(db.sql.json_array(ary))
                    return WhereData("${json_contains.expression} = 1", json_contains.values)
                } else {
                    val json_contains = this.jsonContains(db.sql.json_array(listOf(value)))
                    return WhereData("${json_contains.expression} = 1", json_contains.values)
                }
            }
        }

        throw java.lang.RuntimeException("json_contains要求列必须是JSON类型！")
    }

    infix fun sqlJsonArrayNotContains(value: Serializable): WhereData {
        if (value is SqlColumnName) {
            return WhereData("${this.jsonContains(value)} = 0")
        }

        if (this is SqlColumnName) {
            //仅支持 数组相等。
            if (this.dbType == DbType.JSON) {
                val v_type = value::class.java
                if (v_type.isArray) {
                    val ary = value as Array<Any?>;
                    val json_contains = this.jsonContains(db.sql.json_array(ary.toList()))
                    return WhereData("${json_contains.expression} = 0")
                } else if (v_type.IsCollectionType) {
                    val ary = value as Collection<Any?>;
                    val json_contains = this.jsonContains(db.sql.json_array(ary))
                    return WhereData("${json_contains.expression} = 0")
                } else {
                    val json_contains = this.jsonContains(db.sql.json_array(listOf(value)))
                    return WhereData("${json_contains.expression} = 0")
                }
            }
        }

        throw java.lang.RuntimeException("json_contains要求列必须是JSON类型！")
    }


    infix fun sqlJsonObjectContains(value: Serializable): WhereData {
        if (value is SqlColumnName) {
            return WhereData("${this.jsonContains(value)} = 1")
        }

        if (this is SqlColumnName) {
            //仅支持 数组相等。
            if (this.dbType == DbType.JSON) {
                val json_contains = this.jsonContains(value.ToJson())
                return WhereData("${json_contains.expression} = 1", json_contains.values)
            }
        }

        throw java.lang.RuntimeException("json_contains要求列必须是JSON类型！")
    }

    infix fun sqlJsonObjectNotContains(value: Serializable): WhereData {
        if (value is SqlColumnName) {
            return WhereData("${this.jsonContains(value)} = 0")
        }

        if (this is SqlColumnName) {
            //仅支持 数组相等。
            if (this.dbType == DbType.JSON) {
                val json_contains = this.jsonContains(value.ToJson())
                return WhereData("${json_contains.expression} = 0")
            }
        }

        throw java.lang.RuntimeException("json_contains要求列必须是JSON类型！")
    }

    /**
     * 大于等于操作
     */
    infix fun sqlGreaterThanEquals(value: Serializable): WhereData {
        if (this is SqlColumnName) {
            if (value is SqlColumnName) {
                return WhereData("${this.fullName} >= ${value.fullName}")
            }
        }
        return this.sqlMatch(">=", value)
    }

    /**
     * 大于操作，不包含等于
     */
    infix fun sqlGreaterThan(value: Serializable): WhereData {
        if (this is SqlColumnName) {
            if (value is SqlColumnName) {
                return WhereData("${this.fullName} > ${value.fullName}")
            }
        }
        return this.sqlMatch(">", value);
    }

    /**
     * 小于等于操作。
     */
    infix fun sqlLessThanEquals(value: Serializable): WhereData {
        if (this is SqlColumnName) {
            if (value is SqlColumnName) {
                return WhereData("${this.fullName} <= ${value.fullName}")
            }
        }

        return this.sqlMatch("<=", value)
    }

    /**
     * 小于操作，不包含等于
     */
    infix fun sqlLessThan(value: Serializable): WhereData {
        if (this is SqlColumnName) {
            if (value is SqlColumnName) {
                return WhereData("${this.fullName} < ${value.fullName}")
            }
        }
        return this.sqlMatch("<", value)
    }


    private fun sqlUntil(min: Any, max: Any): WhereData {
        var minValue = proc_value(min);
        var maxValue = proc_value(max);

        if (this is SqlColumnName) {
            return WhereData(
                "${this.fullName} >= :${this.paramVarKeyName}_min and ${this.fullName} < :${this.paramVarKeyName}_max",
                JsonMap(
                    "${this.paramVarKeyName}_min" to minValue,
                    "${this.paramVarKeyName}_max" to maxValue
                )
            )
        }

        var sqlData = this.toSingleSqlData()
        var sqlParameterKey = CodeUtil.getCode();

        sqlData.values += JsonMap(
            "${sqlParameterKey}_min" to minValue,
            "${sqlParameterKey}_max" to maxValue
        )

        return WhereData(
            "${sqlData.expression} >= :${sqlParameterKey}_min and ${sqlData.expression} < :${sqlParameterKey}_max",
            sqlData.values
        )
    }

    /**
     * 开闭区间，表示大于等于 并且 小于
     */
    fun <T : Serializable> sqlUntil(min: T, max: T): WhereData {
        if (this is SqlColumnName) {
            if (min is SqlColumnName && max is SqlColumnName) {
                return WhereData("${this.fullName} >= ${min.fullName} and ${this.fullName} < ${max.fullName}")
            }
        }

        return this.sqlUntil(min, max);
    }


    /**
     * in (values)操作
     */
    infix inline fun <reified T : Serializable> sqlIn(values: Array<T>): WhereData {
        return sqlOpInOrNotIn("in", T::class.java, values)
    }


    fun <T : Any> sqlOpInOrNotIn(inOrNotIn: String, type: Class<T>, values: Array<T>): WhereData {
        if (this is SqlColumnName) {
            if (type == SqlColumnName::class.java) {
                return WhereData(
                    "${this.fullName} ${inOrNotIn} (${
                        values.map { (it as SqlColumnName).fullName }.joinToString(",")
                            .AsString("null")
                    } )"
                )
            }


            var needWrap = this.dbType.needTextWrap()
            var value = values
                .map {
                    var v = proc_value(it)
                    if (needWrap) {
                        return@map "'" + v + "'"
                    }
                    return@map v;
                }
                .joinToString(",")
                .AsString("null")

            return WhereData("${this.fullName} ${inOrNotIn} ( ${value} )");
        }


        var sqlData = this.toSingleSqlData();
        if (type == SqlColumnName::class.java) {
            return WhereData(
                "${sqlData.expression} ${inOrNotIn} (${
                    values.map { (it as SqlColumnName).fullName }
                        .joinToString(",")
                        .AsString("null")
                } )",
                sqlData.values
            )
        }


        var value = values
            .map {
                var v = proc_value(it)
                var v_type = v.javaClass;
                if (v_type.IsNumberType) {
                    return@map v
                }
                return@map "'" + v + "'"
            }
            .joinToString(",")
            .AsString("null")

        return WhereData("${sqlData.expression} in ( ${value} )", sqlData.values);
    }


    /**
     * in 子查询
     */
    infix fun sqlIn(select: SqlQueryClip<*, *>): WhereData {
        var sqlData = this.toSingleSqlData();
        var subSelect = select.toSql()
        var ret = WhereData("${sqlData.expression} in ( ${subSelect.expression} )", sqlData.values)
        ret.values += subSelect.values
        return ret;
    }

    /**
     * not in (values) 操作
     */
    infix inline fun <reified T : Serializable> sqlNotIn(values: Array<T>): WhereData {
        return sqlOpInOrNotIn("not in", T::class.java, values)
    }

    /**
     * not in 子查询
     */
    infix fun sqlNotIn(select: SqlQueryClip<*, *>): WhereData {
        var sqlData = this.toSingleSqlData();
        var subSelect = select.toSql()
        var ret =
            WhereData("${sqlData.expression} not in ( ${subSelect.expression} )", sqlData.values)
        ret.values += subSelect.values
        return ret;
    }


    /**
     * 生成 (col is null or col = 0/'' )
     */
    fun isNullOrEmpty(): WhereData {
        if (this is SqlColumnName) {
            var emptyValue = "";
            if (this.dbType.isNumberic()) {
                emptyValue = " or ${this.fullName} = 0"
            } else if (this.dbType != DbType.OTHER) {
                emptyValue = " or ${this.fullName} = ''";
            }

            return WhereData("(${this.fullName} is null ${emptyValue})")
        }

        /**
         * 当在代码段使用该函数时，可能会因为数据类型（数字0 和空字符串比较）导致全表扫描！
         * 这里，仅与 空字符串相比。
         * 数据库设计上，尽量避免 空值，建议使用 0，空串做默认值！
         */
        var sqlData = this.toSingleSqlData();
        return WhereData(
            "(${sqlData.expression} is null or ${sqlData.expression} = '')",
            sqlData.values
        )
    }


    /**
     * 返回第一个非Null的值，使用 coalesce 替代 ifNull
     * @param elseValues 可以是 BaseAliasSqlSect ，也可以是 简单的值类型
     */
    fun ifNull(vararg elseValues: Serializable): SqlParameterData {
        var ret = this.toSingleSqlData();
        ret.expression = "coalesce(${ret.expression}"

        elseValues.forEach {
            if (it is BaseAliasSqlSect) {
                ret.expression += ","
                ret += it.toSingleSqlData()
                return@forEach
            }

            var type = it.javaClass

            if (type.IsNumberType) {
                ret.expression += ",${it}"
            } else {
                ret.expression += ",'${it}'"
            }
        }
        ret.expression += ")"
        return ret;
    }


    private fun op_ext_sql(baseAliasSqlData: BaseAliasSqlSect, op: String): SqlParameterData {
        if (baseAliasSqlData is SqlParameterData) {
            var clone = baseAliasSqlData.CloneObject()
            clone.expression = "${op}(${clone.expression})"
            return clone;
        } else if (baseAliasSqlData is SqlColumnName) {
            var clone = baseAliasSqlData.toSingleSqlData()
            clone.expression = "${op}(${clone.expression})"
            return clone;
        }

        throw RuntimeException("不识别的类型:${baseAliasSqlData::class.java.name}")
    }


    fun sum(): SqlParameterData {
        return op_ext_sql(this, "sum");
    }

    fun count(): SqlParameterData {
        return op_ext_sql(this, "count");
    }


    fun min(): SqlParameterData {
        return op_ext_sql(this, "min");
    }


    fun max(): SqlParameterData {
        return op_ext_sql(this, "max");
    }


    fun avg(): SqlParameterData {
        return op_ext_sql(this, "avg");
    }


    /**
     * 字符个数
     */
    fun character_length(): SqlParameterData {
        return op_ext_sql(this, "character_length");
    }


    /**
     * 判断该列的内容是否在另一个列内容集合中。 find_in_set('a','a,b,c') = 1
     */
    fun findInSet(arrayColumn: BaseAliasSqlSect): SqlParameterData {
        var sqlData = this.toSingleSqlData();
        var arrayData = arrayColumn.toSingleSqlData()
        var ret =
            SqlParameterData("find_in_set ( ${sqlData.expression} , ${arrayData.expression} ) ", sqlData.values)
        ret.values += arrayData.values
        return ret;
    }


    fun whereInSet(arrayColumn: BaseAliasSqlSect): WhereData {
        var sqlData = this.toSingleSqlData();
        var arrayData = arrayColumn.toSingleSqlData()
        var ret =
            WhereData("find_in_set ( ${sqlData.expression} , ${arrayData.expression} ) > 0", sqlData.values)
        ret.values += arrayData.values
        return ret;
    }

    /**
     * @param separator 会移除非法的 单引号
     */
    fun group_concat(distinct: Boolean = true, order: SqlOrderBy? = null, separator: String = ","): SqlParameterData {
        var sqlData = this.toSingleSqlData();
        var orderData = order?.toSingleSqlData()
        var ret =
            SqlParameterData(
                "GROUP_CONCAT(${if (distinct) "distinct " else ""} name ${
                    if (orderData != null && orderData.expression.HasValue) {
                        " ORDER BY ${orderData.expression}"
                    } else ""
                } SEPARATOR '${separator.replace("'", "")}') ", sqlData.values
            )

        if (orderData != null) {
            ret.values += orderData.values;
        }
        return ret;
    }


    /**
     * 连接多个列,该列是第一个连接列。
     */
    fun concat_ws(separator: String = ",", vararg columns: BaseAliasSqlSect): SqlParameterData {
        var sqlData = this.toSingleSqlData();
        var others = columns.map { it.toSingleSqlData() };

        var ret =
            SqlParameterData(
                "concat_ws('${separator.replace("'", "")}', ${
                    others.map { it.expression }.joinToString(",")
                }) ", sqlData.values
            )

        others.forEach {
            ret.values += it.values;
        }
        return ret;
    }


    /**
     * 返回几个列中值的最大值，不是聚合函数max!
     */
    fun greatest(vararg columns: BaseAliasSqlSect): SqlParameterData {
        var sqlData = this.toSingleSqlData();
        var others = columns.map { it.toSingleSqlData() };

        var ret =
            SqlParameterData(
                "greatest(${
                    others.map { it.expression }.joinToString(",")
                }) ", sqlData.values
            )

        others.forEach {
            ret.values += it.values;
        }
        return ret;
    }


    fun least(vararg columns: BaseAliasSqlSect): SqlParameterData {
        var sqlData = this.toSingleSqlData();
        var others = columns.map { it.toSingleSqlData() };

        var ret =
            SqlParameterData(
                "least(${
                    others.map { it.expression }.joinToString(",")
                }) ", sqlData.values
            )

        others.forEach {
            ret.values += it.values;
        }
        return ret;
    }

    /**
     *
     */
    fun subString(startIndex: Int, length: Int): SqlParameterData {
        var sqlData = this.toSingleSqlData();

        var ret = SqlParameterData(
            "subString (${sqlData.expression} from ${startIndex} for ${length})", sqlData.values
        )

        return ret;
    }

    fun replaceString(oldString: String, column: BaseAliasSqlSect): SqlParameterData {
        var sqlData = this.toSingleSqlData();
        var columnData = column.toSingleSqlData();
        var ret = SqlParameterData(
            "replace (${sqlData.expression} , '${oldString.replace("'", "''")}', ${columnData.expression})",
            sqlData.values
        )

        ret.values += columnData.values;

        return ret;
    }
}